/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sjtu.bean;

import com.sjtu.constants.Constants;
import com.sjtu.database.DataBase;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author p w
 */
public class BookBean {

    private int id;
    private String name;
    private String author;
    private String description;
    private String coverURL;
    private int category;
    private Date time;
    private String publisher;
    private String isbn;
    private int score;
    private ArrayList<LabelBean> labels;
    private String libraryId;

    public BookBean(int id, String name, String author, String description,
            String coverURL, int category, Date time, String publisher, String isbn, int score) {
        this.id = id;
        this.name = name;
        this.author = author;
        this.description = description;
        this.coverURL = coverURL;
        this.category = category;
        this.time = time;
        this.publisher = publisher;
        this.score = score;
        this.isbn = isbn;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public int getCategory() {
        return category;
    }

    public void setCategory(int category) {
        this.category = category;
    }

    public String getCoverURL() {
        return coverURL;
    }

    public void setCoverURL(String coverURL) {
        this.coverURL = coverURL;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPublisher() {
        return publisher;
    }

    public void setPublisher(String publisher) {
        this.publisher = publisher;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }

    public String getLibraryId() {
        return libraryId;
    }

    public void setLibraryId(String libraryId) {
        this.libraryId = libraryId;
    }

    public String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    public static BookBean getBook(int bookid) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from book where id = '" + bookid + "';");
            BookBean book = null;
            if (rs.next()) {
                book = setBook(rs);
            }
            rs.close();
            return book;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static BookBean setBook(ResultSet rs) {
        try {
            if (rs == null) {
                return null;
            }
            return new BookBean(rs.getInt("id"), rs.getString("name"),
                    rs.getString("author"), rs.getString("description"),
                    rs.getString("coverURL"),
                    rs.getInt("categoryId"), rs.getDate("time"),
                    rs.getString("publisher"), rs.getString("ISBN"), rs.getInt("needScore"));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static BookBean addBook(String bookname, String author, String description,
            String coverURL, String category, String publisher,
            String isbn, int score) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from book where name = '" + bookname + "';");
            if (rs.next()) {
                return BookBean.setBook(rs);
            }
            Date dateNow = new Date();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String addDate = dateFormat.format(dateNow);
            CategoryBean bookclass = CategoryBean.getCategory(category);
//            LibraryBean library = LibraryBean.addLibrary(libraryName, libraryAddress);
            String sql = "insert into book (name, author, description, coverURL, categoryId, time, ISBN, publisher," +
                    "needScore) values ('" + bookname + "','" + author + "','" + description + "','" +
                    coverURL + "'," + bookclass.getId() + ",'" + addDate + "','" + isbn + "','" + publisher + "'," + score + ");";
//            System.out.println(sql);
            database.update(sql);
            rs = database.query("select * from book where name = '" + bookname + "';");
//            System.out.println("select * from book where name = '" + bookname + "';");
            if (rs.next()) {
                return BookBean.setBook(rs);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

//    public boolean deleteBook(String bookName,String userName) {
//        BookBean book = BookBean.getBook(id)
//    }
    public static BookBean getByName(String bookName) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from book where name = '" + bookName + "';");
            BookBean book = null;
            if (rs.next()) {
                book = setBook(rs);
            }
            rs.close();
            return book;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static BookBean getByISBN(String isbn) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from book where isbn = '" + isbn + "';");
            BookBean book = null;
            if (rs.next()) {
                book = setBook(rs);
            }
            rs.close();
            return book;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static ArrayList<BookBean> getByAuthor(String authorName) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from book where author = '" + authorName + "';");
            ArrayList<BookBean> booksOfTheAuthor = new ArrayList<BookBean>();
            while (rs.next()) {
                booksOfTheAuthor.add(setBook(rs));
            }
            rs.close();
            return booksOfTheAuthor;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static ArrayList<BookBean> getByPublisher(String publisher) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from book where publisher = '" + publisher + "';");
            ArrayList<BookBean> booksOfThePublisher = new ArrayList<BookBean>();
            while (rs.next()) {
                booksOfThePublisher.add(setBook(rs));
            }
            rs.close();
            return booksOfThePublisher;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static ArrayList<BookBean> getByLabel(String label) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select id from booklabel where content = '" + label + "';");
            ArrayList<Integer> bookIdList = new ArrayList<Integer>();
            ArrayList<BookBean> booksOfTheLabel = new ArrayList<BookBean>();
            int labelId;
            if (!rs.next()) {
                return booksOfTheLabel;
            } else {
                labelId = rs.getInt("id");
            }
            rs = database.query("select bookid from booklabelinfo where labelid = " + labelId + ";");
            System.out.println("select bookid from booklabelinfo where labelid = " + labelId + ";");
            while (rs.next()) {
                bookIdList.add(rs.getInt("bookid"));
            }
            for (int i = 0; i < bookIdList.size(); i++) {
                rs = database.query("select * from book where id = " + bookIdList.get(i) + ";");
                rs.next();
                booksOfTheLabel.add(setBook(rs));
            }
//            while (rs.next()) {
//                booksOfTheLabel.add(setBook(rs));
//            }
            rs.close();
            return booksOfTheLabel;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public void setLabel(String label) {
        if (label.equals("")) {
            return;
        }
//        String[] labelContent = labelString.split("#");
        this.labels = new ArrayList<LabelBean>();
//        for (int i = 0; i < labelContent.length; i++) {
//            label = labelContent[i];
        while (true) {
            if (label.charAt(0) == ' ') {
                label = label.substring(1);
            } else {
                break;
            }
        }
        while (true) {
            if (label.charAt(label.length() - 1) == ' ') {
                label = label.substring(0, label.length() - 1);
            } else {
                break;
            }
        }
        LabelBean labelCon = LabelBean.addLabel(label);
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select id from booklabelinfo where bookId = '" + id + "'and labelId = '" + labelCon.getId() + "';");
            if (!rs.next()) {
                String sql = "insert into booklabelinfo (bookId,labelId) values (" + id + "," + labelCon.getId() + ");";
//                    System.out.println(sql);
                database.update(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.labels.add(labelCon);

    }

    public void setLibrary(String libraryName, String libraryAddress, int bookNumber) {
        LibraryBean library = LibraryBean.addLibrary(libraryName, libraryAddress);
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select num from booklibraryinfo where bookId = '" + id + "'and libraryId = '" + library.getId() + "';");
            if (!rs.next()) {
                String sql = "insert into booklibraryinfo (bookId,libraryId,num) values (" + id + "," + library.getId() + "," + bookNumber + ");";
//                System.out.println(sql);
                database.update(sql);
            } else {
                int num = rs.getInt("num");
                String sql = "update booklibraryinfo set num = " + (num + bookNumber) + " where bookId = '" + id + "'and libraryId = '" + library.getId() + "';";
                database.update(sql);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//    public void deleteBook(int bookId){
//        
//    }
    public ArrayList<String> getLabel() {
        ArrayList<Integer> labelIds = new ArrayList<Integer>();
        ArrayList<String> labels = new ArrayList<String>();
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select labelId from booklabelinfo where bookId = " + id + ";");
            while (rs.next()) {
                labelIds.add(rs.getInt("labelId"));
            }
            for (int i = 0; i < labelIds.size(); i++) {
                rs = database.query("select content from booklabel where id = " + labelIds.get(i) + ";");
                while (rs.next()) {
                    labels.add(rs.getString("content"));
                }
            }
            return labels;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean borrowBook(int userId, int bookId, int libraryId) {
        int booklibraryId = BookBean.getBookLibraryId(bookId, libraryId);
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select score,status from user where id = " + userId + ";");
            rs.next();
            int status = rs.getInt("status");
            int score = rs.getInt("score");
            if (status != Constants.UserStatus_Check) {
                return false;
            }
            rs = database.query("select needScore from book where id = " + bookId + ";");
            rs.next();
            int needScore = rs.getInt("needScore");
            if (score < needScore) {
                return false;
            }
            if (database.update("insert into userbookinfo (userId,bookLibraryInfoId,status) " +
                    "values (" + userId + "," + booklibraryId + "," +
                    Constants.BorrowStatus_BorrowRequest + ");")) {
                rs = database.query("select popularity from book where id = " + bookId + ";");
                rs.next();
                double popular = rs.getDouble("popularity") + 0.7;
                return database.update("update book set popularity = " + popular + "where id = " + bookId + ";");
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean returnBook(int userBookInfoId) {
        DataBase database = DataBase.getInstance();
        return database.update("update userbookinfo set status = " +
                Constants.BorrowStatus_ReturnRequest + " where id = " +
                userBookInfoId + ";");
    }

    public static ArrayList<String> getBookLibraryInfo(int bookId) {
        ArrayList<Integer> LibraryIdList = new ArrayList<Integer>();
        ArrayList<Integer> bookLibrarIdList = new ArrayList<Integer>();
        ArrayList<Integer> bookNumList = new ArrayList<Integer>();
        ArrayList<String> bookResult = new ArrayList<String>();
        int totalNumber = 0;
        try {
            DataBase database = DataBase.getInstance();
            String Librarybook = "";
            ResultSet rs = database.query("select id,libraryId, num from booklibraryinfo where bookId = " + bookId + ";");
            while (rs.next()) {
                bookLibrarIdList.add(rs.getInt("id"));
                LibraryIdList.add(rs.getInt("libraryId"));
                bookNumList.add(rs.getInt("num"));
            }
            for (int i = 0; i < LibraryIdList.size(); i++) {
                Librarybook = LibraryBean.getLibrary(LibraryIdList.get(i)).getName() + "  ";
                rs = database.query("select id from userbookinfo where bookLibraryInfoId = " + bookLibrarIdList.get(i) + ";");
                int outNum = 0;
                while (rs.next()) {
                    outNum++;
                }
                Librarybook += " bookNumber:" + (bookNumList.get(i) - outNum) + "/" + bookNumList.get(i);
                bookResult.add(Librarybook);
                totalNumber += (bookNumList.get(i) - outNum);
            }
            bookResult.add(totalNumber + "");
            return bookResult;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static int getBookLibraryId(int bookId, int LibraryId) {
        try {
            DataBase database = DataBase.getInstance();
            String sql;
            ResultSet rs = database.query("select id from booklibraryinfo where bookId = " + bookId + " and libraryId = " + LibraryId + ";");
            if (rs.next()) {
                return rs.getInt("id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static ArrayList<String> borrowBookList() {
        ArrayList<Integer> userIdList = new ArrayList<Integer>();
        ArrayList<Integer> bookLibraryInfoIdList = new ArrayList<Integer>();
        ArrayList<String> borrowBookList = new ArrayList<String>();
//        ArrayList<Integer> bookLibraryInfoIdList = new ArrayList<Integer>();
        try {
            String userName;
            int bookId;
            int libraryId;
            String bookName;
            String libraryName;
            DataBase database = DataBase.getInstance();
            String sql;
            ResultSet rs = database.query("select id,userId,bookLibraryInfoId from userbookinfo where status = 0;");
            while (rs.next()) {
                userIdList.add(rs.getInt("userId"));
                bookLibraryInfoIdList.add(rs.getInt("bookLibraryInfoId"));
            }
            for (int i = 0; i < userIdList.size(); i++) {
                userName = UserBean.getUser(userIdList.get(i)).getName();
                rs = database.query("select bookId,libraryId from booklibraryinfo where id = " + bookLibraryInfoIdList.get(i) + ";");
                rs.next();
                bookId = rs.getInt("bookId");
                libraryId = rs.getInt("libraryId");
                rs = database.query("select name from book where id = " + bookId + ";");
                rs.next();
                bookName = rs.getString("name");
                rs = database.query("select name from library where id = " + libraryId + ";");
                rs.next();
                libraryName = rs.getString("name");
                borrowBookList.add(userName + "#" + bookName + "#" + libraryName + "#" + bookLibraryInfoIdList.get(i));
            }
            return borrowBookList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<String> returnBookList() {
        ArrayList<Integer> userIdList = new ArrayList<Integer>();
        ArrayList<Integer> bookLibraryInfoIdList = new ArrayList<Integer>();
        ArrayList<Integer> userBookInfoIdList = new ArrayList<Integer>();
        ArrayList<String> startDateList = new ArrayList<String>();
        ArrayList<String> endDateList = new ArrayList<String>();
        ArrayList<String> returnBookList = new ArrayList<String>();
        try {
            String userName;
            int bookId;
            int libraryId;
            String bookName;
            String libraryName;
            DataBase database = DataBase.getInstance();
            String sql;
            ResultSet rs = database.query("select id,userId,bookLibraryInfoId,startDate,endDate from userbookinfo where status = 3;");
            while (rs.next()) {
                userBookInfoIdList.add(rs.getInt("id"));
                userIdList.add(rs.getInt("userId"));
                bookLibraryInfoIdList.add(rs.getInt("bookLibraryInfoId"));
                startDateList.add(rs.getString("startDate"));
                endDateList.add(rs.getString("endDate"));
            }
            for (int i = 0; i < userIdList.size(); i++) {
                userName = UserBean.getUser(userIdList.get(i)).getName();
                rs = database.query("select bookId,libraryId from booklibraryinfo where id = " + bookLibraryInfoIdList.get(i) + ";");
                rs.next();
                bookId = rs.getInt("bookId");
                libraryId = rs.getInt("libraryId");
                rs = database.query("select name from book where id = " + bookId + ";");
                rs.next();
                bookName = rs.getString("name");
                rs = database.query("select name from library where id = " + libraryId + ";");
                rs.next();
                libraryName = rs.getString("name");
                returnBookList.add(userName + "#" + bookName + "#" + userBookInfoIdList.get(i) + "#" + startDateList.get(i) + "#" + endDateList.get(i));
            }
            return returnBookList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<String> userBorrowList(int userId) {
//        ArrayList<Integer> userIdList = new ArrayList<Integer>();
        ArrayList<Integer> bookLibraryInfoIdList = new ArrayList<Integer>();
        ArrayList<String> startDateList = new ArrayList<String>();
        ArrayList<String> endDateList = new ArrayList<String>();
        ArrayList<String> returnBookList = new ArrayList<String>();
        ArrayList<Integer> statusList = new ArrayList<Integer>();
        try {
//            String userName;
            int bookId;
            int libraryId;
            String bookName;
            String libraryName;
            DataBase database = DataBase.getInstance();
            String sql;
            ResultSet rs = database.query("select bookLibraryInfoId,startDate,endDate,status from userbookinfo where status = 1 or 2 or 0 and userId = " + userId + ";");
            while (rs.next()) {
//                userIdList.add(rs.getInt("userId"));
                statusList.add(rs.getInt("status"));
                bookLibraryInfoIdList.add(rs.getInt("bookLibraryInfoId"));
                startDateList.add(rs.getString("startDate"));
                endDateList.add(rs.getString("endDate"));
            }
            for (int i = 0; i < bookLibraryInfoIdList.size(); i++) {
//                userName = UserBean.getUser(userId).getName();
                rs = database.query("select bookId,libraryId from booklibraryinfo where id = " + bookLibraryInfoIdList.get(i) + ";");
                rs.next();
                bookId = rs.getInt("bookId");
                libraryId = rs.getInt("libraryId");
                rs = database.query("select name from book where id = " + bookId + ";");
                rs.next();
                bookName = rs.getString("name");
                rs = database.query("select name from library where id = " + libraryId + ";");
                rs.next();
                libraryName = rs.getString("name");
                returnBookList.add(bookName + "#" + libraryName + "#" + startDateList.get(i) + "#" + endDateList.get(i) + "#" + statusList.get(i));
            }
            return returnBookList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<String> userreturnList(int userId) {
        ArrayList<Integer> userbookinfoIdList = new ArrayList<Integer>();
        ArrayList<Integer> bookLibraryInfoIdList = new ArrayList<Integer>();
        ArrayList<String> startDateList = new ArrayList<String>();
        ArrayList<String> endDateList = new ArrayList<String>();
        ArrayList<String> returnBookList = new ArrayList<String>();
        try {
//            String userName;
            int bookId;
            int libraryId;
            String bookName;
            String libraryName;
            DataBase database = DataBase.getInstance();
            String sql;
            ResultSet rs = database.query("select id,bookLibraryInfoId,startDate,endDate from userbookinfo where status = 1 and userId = " + userId + ";");
            while (rs.next()) {
//                userIdList.add(rs.getInt("userId"));
                userbookinfoIdList.add(rs.getInt("id"));
                bookLibraryInfoIdList.add(rs.getInt("bookLibraryInfoId"));
                startDateList.add(rs.getString("startDate"));
                endDateList.add(rs.getString("endDate"));
            }
            for (int i = 0; i < bookLibraryInfoIdList.size(); i++) {
//                userName = UserBean.getUser(userId).getName();
                rs = database.query("select bookId,libraryId from booklibraryinfo where id = " + bookLibraryInfoIdList.get(i) + ";");
                rs.next();
                bookId = rs.getInt("bookId");
                libraryId = rs.getInt("libraryId");
                rs = database.query("select name from book where id = " + bookId + ";");
                rs.next();
                bookName = rs.getString("name");
                rs = database.query("select name from library where id = " + libraryId + ";");
                rs.next();
                libraryName = rs.getString("name");
                returnBookList.add(bookName + "#" + libraryName + "#" + startDateList.get(i) + "#" + endDateList.get(i) + "#" + userbookinfoIdList.get(i));
            }
            return returnBookList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Boolean checkBorrowBook(int bookLibraryId, int Date, int status) {
        Date dateNow = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String createDate = dateFormat.format(dateNow);
        Date returnDate = new Date();
        returnDate.setDate(returnDate.getDate() + Date);
        String endDate = dateFormat.format(returnDate);
        String sql = "update userbookinfo set startDate = '" + createDate + "',endDate = '" + endDate + "',status = " + status + " where bookLibraryInfoId = '" + bookLibraryId + "';";
        System.out.println(sql);
        try {
            DataBase database = DataBase.getInstance();
            return database.update(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static Boolean checkReturnBook(int userBookInfoId, int status) {
        DataBase database = DataBase.getInstance();
        return database.update("update userbookinfo set status = " +
                status + " where id = " +
                userBookInfoId + ";");
    }

    public static ArrayList<BookBean> getBookByCategory(String catgoryName) {
        ArrayList<BookBean> categoryBook = new ArrayList<BookBean>();
        String sql;
        try {
            DataBase database = DataBase.getInstance();
            sql = "select id from category where content = '" + catgoryName + "';";
            ResultSet rs = database.query(sql);
            rs.next();
            int categoryId = rs.getInt("id");
            sql = "select * from book where categoryId = " + categoryId + ";";
            rs = database.query(sql);
            while (rs.next()) {
                BookBean book = BookBean.setBook(rs);
                categoryBook.add(book);
            }
            return categoryBook;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Boolean updateScore(
            int bookId, int score) {
        String sql = "update book set needScore = " + score + " where id = " + bookId + ";";
        System.out.println(sql);
        try {
            DataBase database = DataBase.getInstance();
            return database.update(sql);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }

    public static int avgScore(int bookId) {
        String sql = "select avg(score) from comment where bookId = " + bookId + ";";
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query(sql);
            rs.next();
            int bookScore = rs.getInt("avg(score)");
            return bookScore;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int deleteBook(int bookId) {
        String sql = "select id from booklibraryinfo where bookId = " + bookId + ";";
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query(sql);
            rs.next();
            int booklibraryinfoId = rs.getInt("id");
            sql =
                    "select id from userbookinfo where booklibraryinfoId = " + booklibraryinfoId + ";";
            rs =
                    database.query(sql);
            if (rs.next() == true) {
                return -1;
            } else {
                sql = "delete from booklabelinfo where bookId = " + bookId + ";";
                database.update(sql);
                sql =
                        "delete from booklibraryinfo where bookId = " + bookId + ";";
                database.update(sql);
                return 1;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;
    }

    public static ArrayList<BookBean> resentBook() {
        ArrayList<BookBean> resentBook = new ArrayList<BookBean>();
        String sql;
        try {
            DataBase database = DataBase.getInstance();
            sql = "select * from book order by time DESC;";
            ResultSet rs = database.query(sql);
            int i = 0;
            while (rs.next() && i < 5) {
                BookBean book = BookBean.setBook(rs);
                resentBook.add(book);
                i++;
            }
            return resentBook;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<BookBean> hotBook() {
        ArrayList<BookBean> resentBook = new ArrayList<BookBean>();
        String sql;
        try {
            DataBase database = DataBase.getInstance();
            sql = "select * from book order by time DESC;";
            ResultSet rs = database.query(sql);
            int i = 0;
            while (rs.next() && i <= 5) {
                BookBean book = BookBean.setBook(rs);
                resentBook.add(book);
                i++;
            }
            return resentBook;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<BookBean> topBook() {
        ArrayList<BookBean> topBook = new ArrayList<BookBean>();
        String sql;
        try {
            DataBase database = DataBase.getInstance();
            sql = "select * from book order by popularity DESC;";
            ResultSet rs = database.query(sql);
            int i = 0;
            while (rs.next() && i < 5) {
                BookBean book = BookBean.setBook(rs);
                topBook.add(book);
                i++;
            }
            return topBook;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean addWaitingList(int userId, int bookId) {
        try {
            DataBase database = DataBase.getInstance();
            String sql = "insert into waitingList (userId,bookId) values (" + userId + "," + bookId + ");";
            return database.update(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static int waitingNumber(int bookId){
        try{
            DataBase database = DataBase.getInstance();
            String sql = "select count(userId) from  waitingList where bookId = "+  bookId + ";";
            ResultSet rs = database.query(sql);
            rs.next();
            return rs.getInt("count(userId)");
        }catch(Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    public static void main(String args[]) {
        //   ArrayList<BookBean> users = BookBean.topBook();
        BookBean.waitingNumber(5);
        System.out.println("  ");
    }
}
